﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public class Usertab_RoleDal
    {
        string str = "server = LAPTOP-KE3OLTQM; database = RenLiZiYuanGuanli; uid = sa ; pwd = 123456";
        /// <summary>
        /// 添加用户信息
        /// </summary>
        /// <param name="user">用户类</param>
        /// <returns></returns>
        public int AddUser(Models.Usertab_Role user)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand command = new SqlCommand("addUser", coon);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = { 
                                        new SqlParameter("@username",SqlDbType.NVarChar,100),
                                        new SqlParameter("@userpass",SqlDbType.VarChar,200),
                                        new SqlParameter("@userphone",SqlDbType.VarChar,20),
                                        new SqlParameter("@useremail",SqlDbType.VarChar,100),
                                        new SqlParameter("@realname",SqlDbType.VarChar,100),
                                        new SqlParameter("@usersex",SqlDbType.NVarChar,10),
                                        new SqlParameter("@userage",SqlDbType.Int),
                                        new SqlParameter("@userstate",SqlDbType.NVarChar,10),
                                        new SqlParameter("@roleid",SqlDbType.Int),
                                        new SqlParameter("@userdel",SqlDbType.Int)
                                      };
                pars[0].Value = user.userName;
                pars[1].Value = user.userPass;
                pars[2].Value = user.userPhone;
                pars[3].Value = user.userEmail;
                pars[4].Value = user.userRealname;
                pars[5].Value = user.userSex;
                pars[6].Value = user.userAge;
                pars[7].Value = user.userState;
                pars[8].Value = user.RoleId;
                pars[9].Value = user.userDel;
                command.Parameters.AddRange(pars);
                try
                {
                    return command.ExecuteNonQuery();
                }
                catch
                {
                    return 0;
                }
                finally
                {
                    coon.Close();
                    coon.Dispose();
                }
            }
        }
        /// <summary>
        /// 用户信息判断
        /// </summary>
        /// <param name="del"></param>
        /// <returns></returns>
        public List<Models.Usertab_Role> userShowpd(Models.Usertab_Role del)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand command = new SqlCommand("selUserpd", coon);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = {
                                    new SqlParameter("@name",SqlDbType.NVarChar,100),
                                    new SqlParameter("@pass",SqlDbType.VarChar,200),
                                  };
                pars[0].Value = del.userName;
                pars[1].Value = del.userPass;
                command.Parameters.AddRange(pars);
                SqlDataReader reader = command.ExecuteReader();
                List<Models.Usertab_Role> list = new List<Usertab_Role>();
                while (reader.Read())
                {
                    Models.Usertab_Role user = new Usertab_Role();
                    user.userID = Convert.ToInt32(reader["userID"]);
                    user.userName = reader["userName"].ToString();
                    user.userPass = reader["userPass"].ToString();
                    user.userPhone = reader["userPhone"].ToString();
                    user.userEmail = reader["userEmail"].ToString();
                    user.userRealname = reader["userRealname"].ToString();
                    user.userSex = reader["userSex"].ToString();
                    user.userAge = Convert.ToInt32(reader["userAge"]);
                    user.userState = reader["userState"].ToString();
                    user.userDel = Convert.ToInt32(reader["userDel"]);
                    user.RoleId = Convert.ToInt32(reader["RoleId"]);
                    list.Add(user);
                }
                try
                {
                    return list;
                }
                catch
                {
                    return new List<Usertab_Role>();
                }
                finally
                {
                    coon.Close();
                    coon.Dispose();
                }
            }
        }
        /// <summary>
        /// 用户信息查找
        /// </summary>
        /// <param name="del"></param>
        /// <returns></returns>
        public List<Models.Usertab_Role> idShow(int userid)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand command = new SqlCommand("IDshow", coon);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = {
                                    new SqlParameter("@userid",SqlDbType.Int)
                                  };
                pars[0].Value = userid;
                command.Parameters.AddRange(pars);
                SqlDataReader reader = command.ExecuteReader();
                List<Models.Usertab_Role> list = new List<Usertab_Role>();
                while (reader.Read())
                {
                    Models.Usertab_Role user = new Usertab_Role();
                    user.userID = Convert.ToInt32(reader["userID"]);
                    user.userName = reader["userName"].ToString();
                    user.userPhone = reader["userPhone"].ToString();
                    user.userEmail = reader["userEmail"].ToString();
                    user.userRealname = reader["userRealname"].ToString();
                    user.userSex = reader["userSex"].ToString();
                    user.userAge = Convert.ToInt32(reader["userAge"]);
                    user.userState = reader["userState"].ToString();
                    user.RoleId = Convert.ToInt32(reader["RoleId"]);
                    list.Add(user);
                }
                try
                {
                    return list;
                }
                catch
                {
                    return new List<Usertab_Role>();
                }
                finally
                {
                    coon.Close();
                    coon.Dispose();
                }
            }
        }
        /// <summary>
        /// 用户信息分页
        /// </summary>
        /// <param name="del"></param>
        /// <returns></returns>
        public List<Models.Usertab_Role> userShow(out int zongCount, int pageIndex, int pageSize, string userName)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand command = new SqlCommand("userPage", coon);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = { 
                                        new SqlParameter ("@zongCount",SqlDbType.Int ),
                                        new SqlParameter ("@pageIndex",SqlDbType.Int ),
                                        new SqlParameter ("@pageSize",SqlDbType.Int ),
                                        new SqlParameter ("@orderString",SqlDbType.VarChar,100 ),
                                        new SqlParameter ("@whereString",SqlDbType.VarChar,100 )
                                      };
                pars[0].Direction = ParameterDirection.Output;
                pars[1].Value = pageIndex;
                pars[2].Value = pageSize;
                pars[3].Value = "userID";
                if (userName == null || userName == "")
                {
                    pars[4].Value = "userShan = 0";
                }
                else
                {
                    pars[4].Value = "userName like " + "'%" + userName + "%'" + " and userShan = 0";
                }
                command.Parameters.AddRange(pars);

                List<Models.Usertab_Role> list = new List<Usertab_Role>();
                SqlDataReader reader = command.ExecuteReader();


                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Models.Usertab_Role user = new Usertab_Role();
                        user.userID = Convert.ToInt32(reader["userID"]);
                        user.userName = reader["userName"].ToString();
                        user.userPhone = reader["userPhone"].ToString();
                        user.userEmail = reader["userEmail"].ToString();
                        user.userRealname = reader["userRealname"].ToString();
                        user.userSex = reader["userSex"].ToString();
                        user.userAge = Convert.ToInt32(reader["userAge"]);
                        user.userState = reader["userState"].ToString();
                        user.userDel = Convert.ToInt32(reader["userDel"]);
                        user.userShan = Convert.ToInt32(reader["userShan"]);
                        user.RoleId = Convert.ToInt32(reader["RoleId"]);
                        list.Add(user);
                    }
                    reader.Close();
                    zongCount = Convert.ToInt32(pars[0].Value);
                }
                else
                {
                    zongCount = 0;
                }
                try
                {
                    return list;
                }
                catch
                {
                    return new List<Usertab_Role>();
                }
                finally
                {
                    coon.Close();
                    coon.Dispose();
                }
            }
        }
        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="user">用户类</param>
        /// <returns></returns>
        public int delUser(Models.Usertab_Role user)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand command = new SqlCommand("delUser", coon);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = { 
                                        new SqlParameter("@username",SqlDbType.NVarChar,100),
                                        new SqlParameter("@userphone",SqlDbType.VarChar,20),
                                        new SqlParameter("@useremail",SqlDbType.VarChar,100),
                                        new SqlParameter("@realname",SqlDbType.VarChar,100),
                                        new SqlParameter("@usersex",SqlDbType.NVarChar,10),
                                        new SqlParameter("@userage",SqlDbType.Int),
                                        new SqlParameter("@userstate",SqlDbType.NVarChar,10),
                                        new SqlParameter("@roleid",SqlDbType.Int),
                                        new SqlParameter("@uid",SqlDbType.Int)
                                      };
                pars[0].Value = user.userName;
                pars[1].Value = user.userPhone;
                pars[2].Value = user.userEmail;
                pars[3].Value = user.userRealname;
                pars[4].Value = user.userSex;
                pars[5].Value = user.userAge;
                pars[6].Value = user.userState;
                pars[7].Value = user.RoleId;
                pars[8].Value = user.userID;
                command.Parameters.AddRange(pars);
                try
                {
                    return command.ExecuteNonQuery();
                }
                catch
                {
                    return 0;
                }
                finally
                {
                    coon.Close();
                    coon.Dispose();
                }
            }
        }
        /// <summary>
        /// 冻结、解冻用户、删除用户
        /// </summary>
        /// <param name="userdel"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int userDong(int userdel, int usershan, int userid)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand command = new SqlCommand("userDong", coon);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = { 
                                        new SqlParameter("@userdel",SqlDbType.Int),
                                        new SqlParameter ("@usershan",SqlDbType.Int),
                                        new SqlParameter("@userID",SqlDbType.Int)
                                      };
                pars[0].Value = userdel;
                pars[1].Value = usershan;
                pars[2].Value = userid;
                command.Parameters.AddRange(pars);

                try
                {
                    return command.ExecuteNonQuery();
                }
                catch
                {
                    return 0;
                }
                finally
                {
                    coon.Close();
                    coon.Dispose();
                }
            }
        }
        /// <summary>
        /// 角色信息读取
        /// </summary>
        /// <returns></returns>
        public List<Models.Usertab_Role> showRole()
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand command = new SqlCommand("showRole", coon);
                command.CommandType = CommandType.StoredProcedure;
                SqlDataReader reader = command.ExecuteReader();
                List<Models.Usertab_Role> rolelist = new List<Usertab_Role>();
                while (reader.Read())
                {
                    Models.Usertab_Role del = new Usertab_Role();
                    del.RoleId = Convert.ToInt32(reader["RoleId"]);
                    del.RoleName = reader["RoleName"].ToString();
                    rolelist.Add(del);
                }
                try
                {
                    return rolelist;
                }
                catch
                {
                    return new List<Usertab_Role>();
                }
                finally
                {
                    coon.Close();
                    coon.Dispose();
                }

            }
        }
        /// <summary>
        /// 角色信息添加
        /// </summary>
        /// <returns></returns>
        public int addRole(string rolename)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand command = new SqlCommand("addRole", coon);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter pars = new SqlParameter("@rolename", SqlDbType.NVarChar, 200);
                pars.Value = rolename;
                command.Parameters.Add(pars);
                try
                {
                    return command.ExecuteNonQuery();
                }
                catch
                {
                    return 0;
                }
                finally
                {
                    coon.Close();
                    coon.Dispose();
                }

            }
        }
        /// <summary>
        /// 角色信息删除
        /// </summary>
        /// <returns></returns>
        public int delRole(int roleid)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand command = new SqlCommand("delRole", coon);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter pars = new SqlParameter("@id", SqlDbType.Int);
                pars.Value = roleid;
                command.Parameters.Add(pars);
                try
                {
                    return command.ExecuteNonQuery();
                }
                catch
                {
                    return 0;
                }
                finally
                {
                    coon.Close();
                    coon.Dispose();
                }

            }
        }
        /// <summary>
        /// 角色信息修改
        /// </summary>
        /// <returns></returns>
        public int updRole(int roleid, string rolename)
        {
            using (SqlConnection coon = new SqlConnection(str))
            {
                if (coon.State == ConnectionState.Closed)
                {
                    coon.Open();
                }
                SqlCommand command = new SqlCommand("updRole", coon);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = {
                                          new SqlParameter("@roleid", SqlDbType.Int),
                                          new SqlParameter("@rolename",SqlDbType.NVarChar,100)
                                    };
                pars[0].Value = roleid;
                pars[1].Value = rolename;
                command.Parameters.AddRange(pars);
                try
                {
                    return command.ExecuteNonQuery();
                }
                catch
                {
                    return 0;
                }
                finally
                {
                    coon.Close();
                    coon.Dispose();
                }

            }
        }



    }
}
